
-------------strat Dirty read (Tuyen)-----------------------
if object_id('sp_TraCuuTuyen','p')
	is not null
drop proc sp_TraCuuTuyen
go
create proc sp_TraCuuTuyen
@matuyen nvarchar(50),@tentuyen nvarchar(50),@noidi nvarchar(50), @noiden nvarchar(50), @dodai nvarchar(50),
@trangthai nvarchar(50), @hesogiave nvarchar(50), @mota nvarchar(max), @malichtrinh nvarchar(50)
as
begin tran
	--set tran isolation level read uncommitted
	set tran isolation level read committed
	declare @query nvarchar(600), @flag int
	set @query ='select * from tuyen'
	set @flag = 0
		
	if(@matuyen<>0)
		begin
			set @query = @query +' where matuyen = ' + @matuyen
			set @flag = 1
		end
		
	if(@tentuyen<>'')
		begin
			if(@flag = 1)
				set @query = @query +' and tentuyen like ' + 'N''%' + @tentuyen + '%''' 
			else
				begin					
					set @query = @query +' where tentuyen like ' + 'N''%' + @tentuyen + '%''' 
					set @flag = 1
				end
		end
			
	if(@noidi<>'')
		begin
			if(@flag = 1)
				set @query = @query +' and noidi ='+N'@noidi'
			else
				begin
				set @query = @query +' where noidi ='+N'@noidi'
					set @flag = 1
				end
		end

	if(@noiden<>'')
		begin
			if(@flag = 1)
				set @query = @query +' and noiden ='+N'@noiden'
			else
				begin
				set @query = @query +' where noiden ='+N'@noiden'
					set @flag = 1
				end
		end
	if(@dodai<>'')
		begin
			if(@flag = 1)
				set @query = @query +' and dodai = ' + @dodai
			else
				begin
				set @query = @query +' where dodai =' + @dodai
					set @flag = 1
				end
		end
	if(@hesogiave<>0)
		begin
			if(@flag = 1)
				set @query = @query +' and hesogiave = ' + @hesogiave
			else
				begin
				set @query = @query +' where hesogiave =' + @hesogiave
					set @flag = 1
				end
		end
	if(@trangthai<>0)
		begin
			if(@flag = 1)
				set @query = @query +' and trangthai ='+ @trangthai
			else
				begin
				set @query = @query +' where trangthai ='+ @trangthai
					set @flag = 1
				end
		end
			
	if(@mota<>'')
		begin
			if(@flag = 1)
				set @query = @query +' and @mota like ' + N'@mota'
			else
				begin
				set @query = @query +' where mota like' + N'@mota'
					set @flag = 1
				end
		end
	if(@malichtrinh<>0)
		begin
			if(@flag = 1)
				set @query = @query +' and malichtrinh = ' + @malichtrinh
			else
				begin
				set @query = @query +' where malichtrinh =' + @malichtrinh
					set @flag = 1
				end
		end
	print(@query)
	exec(@query)
	if(@@error<> 0)begin
		rollback tran
		return
	end
commit

exec sp_TraCuuTuyen '','HCM','','','','','','',''


select * from tuyen where tentuyen like N'%HCM%'



--------------------------------------------------------

if object_id('sp_ThemTuyen', 'p') is not null
drop proc sp_ThemTuyen
go
create proc sp_ThemTuyen
@matuyen int, @tentuyen nvarchar(50),
@noidi nvarchar(50),@noiden nvarchar(50), 
@dodai int, @trangthai int, 
@hesogiave float, @mota nvarchar(MAX) , @malichtrinh int
as 
begin tran
--set tran isolation level read uncommitted
set tran isolation level read committed
	if (@matuyen = 0)
        insert into tuyen
		values(@tentuyen, @noidi, @noiden, @dodai, @trangthai, @hesogiave, @mota, @malichtrinh)
    else
		update tuyen
		set tentuyen = @tentuyen,
			noidi = @noidi,
			noiden = @noiden,
			dodai = @dodai,
			trangthai = @trangthai,
			hesogiave = @hesogiave,
			malichtrinh = @malichtrinh,
			mota = @mota
		where matuyen = @matuyen
		waitfor delay'0:00:10'
        if(@@error <> 0)begin
                rollback tran
                return
        end
	rollback 
commit tran

exec sp_ThemTuyen '0','abc','xczvxvz','xcvzvcxvz','30','1','20','sdfadfs','1'
-------------end Dirty read (Tuyen)-----------------------

----------------------------------------------------------
if object_id('sp_TraCuuTuyenTheoMa', 'p') is not null
drop proc sp_TraCuuTuyenTheoMa
go
create proc sp_TraCuuTuyenTheoMa
        @matuyen int
as
begin
        select * from tuyen
        where matuyen = @matuyen
        
        if(@@error <> 0)begin
                rollback tran
                return
        end
end
exec sp_TraCuuTuyenTheoMa '2'
-------------start Lost update (Tuyen)-----------------------
if object_id('sp_CapNhatHeSoGiaVe', 'p') is not null
drop proc sp_CapNhatHeSoGiaVe
go
create proc  sp_CapNhatHeSoGiaVe
@matuyen int, @heso float
as
begin  tran
	declare  @hesogiave float
	set  @hesogiave =  (select  hesogiave from  tuyen with (updlock) where matuyen=@matuyen)
	--set  @hesogiave =  (select  hesogiave from  tuyen where matuyen=@matuyen)
	waitfor  delay  '0:00:10'
	set  @hesogiave=@hesogiave + @heso
	update  tuyen
	set  hesogiave = @hesogiave
	where  matuyen=@matuyen
commit
-------------end Lost update (Tuyen)-----------------------

----------------------------------------------------------
if object_id('sp_XoaTuyen', 'p') is not null
drop proc sp_XoaTuyen
go
create proc sp_XoaTuyen
        @matuyen int
as
begin
        delete from tuyen
        where matuyen = @matuyen
        
        if(@@error <> 0)begin
                rollback tran
                return
        end
end

--------------------------------------------------------

if object_id('sp_DanhSachLichTrinh', 'p') is not null
drop proc sp_DanhSachLichTrinh
go
create proc sp_DanhSachLichTrinh
        @malichtrinh nvarchar(100), @mota nvarchar(MAX)
as
begin
		declare @query nvarchar(600), @flag int
        set @query = 'select * from lichtrinh'
        if(@malichtrinh<>'')
		begin
			set @query = @query +' where malichtrinh = ' + @malichtrinh
			set @flag = 1
		end

		if(@mota<>'')
		begin
			if(@flag = 1)
				set @query = @query +' and mota ='+ @mota
			else
				begin
					set @query = @query +' where mota ='+ @mota
					set @flag = 1
				end
		end
		print(@query)
		exec (@query)
        if(@@error <> 0)
		begin
                rollback tran
                return
        end
end
exec sp_DanhSachLichTrinh '',''
--------------------------------------------------------
if object_id('sp_ThemLichTrinh', 'p') is not null
drop proc sp_ThemLichTrinh
go
create proc sp_ThemLichTrinh
        @malichtrinh int, @mota nvarchar(MAX)
as
	begin
        if (@malichtrinh = 0)
        insert into lichtrinh
		values(@mota)
    else
		update lichtrinh
		set mota = @mota
		where malichtrinh = @malichtrinh
    if(@@error <> 0)
	begin
		rollback tran
		return
    end
end

exec sp_ThemLichTrinh '1','sdfjlsdf'

--------------------------------------------------------
if object_id('sp_XoaLichTrinh', 'p') is not null
	drop proc sp_XoaLichTrinh
go
create proc sp_XoaLichTrinh
        @malichtrinh int
as
begin
        delete from lichtrinh
        where malichtrinh = @malichtrinh
        
        if(@@error <> 0)begin
                rollback tran
                return
        end
end

--------------------------------------------------------

if object_id('sp_DanhSachDaiLyLichTrinh', 'p') is not null
	drop proc sp_DanhSachDaiLyLichTrinh
go
create proc sp_DanhSachDaiLyLichTrinh
        @malichtrinh int, @madaily int, @thoigianqua datetime
as
begin    
        select * from dailylichtrinh		
        if(@@error <> 0)
		begin
                rollback tran
                return
        end
end

exec sp_DanhSachDaiLyLichTrinh '','',''


--------------------------------------------------------

if object_id('sp_DanhSachDaiLy', 'p') is not null
	drop proc sp_DanhSachDaiLy
go
create proc sp_DanhSachDaiLy        
as
begin    
        select * from daily		
        if(@@error <> 0)
		begin
                rollback tran
                return
        end
end

exec sp_DanhSachDaiLy 

--------------------------------------------------------
if object_id('sp_ThemDaiLyLichTrinh', 'p') is not null
drop proc sp_ThemDaiLyLichTrinh
go
create proc sp_ThemDaiLyLichTrinh
        @malichtrinh int, @madaily int, @thoigianqua datetime
as
begin
        
		update dailylichtrinh
		set thoigianqua = @thoigianqua
		where malichtrinh = @malichtrinh and madaily = @madaily
    if(@@error <> 0)
	begin
		rollback tran
		return
    end
end

--------------------------------------------------------
if object_id('sp_XoaDaiLyLichTrinh', 'p') is not null
drop proc sp_XoaDaiLyLichTrinh
go
create proc sp_XoaDaiLyLichTrinh
        @malichtrinh int, @madaily int
as
begin        
		delete from dailylichtrinh
		where malichtrinh = @malichtrinh and madaily = @madaily
    if(@@error <> 0)
	begin
		rollback tran
		return
    end
end

--------------------------------------------------------
if object_id('sp_DangNhapNhanVien', 'p') is not null
drop proc sp_DangNhapNhanVien
go
create proc sp_DangNhapNhanVien
        @tendangnhap nvarchar(50), @matkhau nvarchar(50)
as
begin
	select * from nhanvien
	where tendangnhap=@tendangnhap and matkhau = @matkhau
	if(@@error <> 0)
	begin
		rollback tran
		return
    end
end

exec sp_DangNhapNhanVien 'tcp','123'

--------------------------------------------------------
if object_id('sp_LayChucVuTheoMa', 'p') is not null
drop proc sp_LayChucVuTheoMa
go
create proc sp_LayChucVuTheoMa
        @macv int
as
begin
	select * from chucvu
	where macv=@macv
	if(@@error <> 0)
	begin
		rollback tran
		return
    end
end

exec sp_LayChucVuTheoMa 1

--------------------------------------------------------
if object_id('sp_LayPhanQuyenTheoMa', 'p') is not null
drop proc sp_LayPhanQuyenTheoMa
go
create proc sp_LayPhanQuyenTheoMa
        @maphanquyen int
as
begin
	select * from phanquyen
	where maphanquyen = @maphanquyen
	if(@@error <> 0)
	begin
		rollback tran
		return
    end
end

exec sp_LayPhanQuyenTheoMa 1


--------------------------------------------------------
if object_id('sp_DanhSachKhoHang', 'p') is not null
drop proc sp_DanhSachKhoHang
go
create proc sp_DanhSachKhoHang
as
begin
	select * from khohang	
	if(@@error <> 0)
	begin
		rollback tran
		return
    end
end

exec sp_DanhSachKhoHang
--------------------------------------------------------
if object_id('sp_TraCuuHangTheoMa', 'p') is not null
drop proc sp_TraCuuHangTheoMa
go
create proc sp_TraCuuHangTheoMa
@makho int
as
begin
	select * from hang	
	where makho = @makho
	if(@@error <> 0)
	begin
		rollback tran
		return
    end
end
exec sp_TraCuuHangTheoMa 2

--------------------------------------------------------
if object_id('sp_ThemKhoHang', 'p') is not null
drop proc sp_ThemKhoHang
go
create proc sp_ThemKhoHang
@makho int, @tenkho nvarchar(50), @mota nvarchar(max), @manv int
as
begin
	if (@makho = 0)
        insert into khohang
        values(@tenkho, @mota, @manv)
    else
		update khohang
		set	tenkho = @tenkho,
			mota = @mota,
			manv = @manv
		where makho = @makho
    if(@@error <> 0)begin
                rollback tran
                return
    end
end
--------------------------------------------------------
if object_id('sp_XoaKhoHang', 'p') is not null
drop proc sp_XoaKhoHang
go
create proc sp_XoaKhoHang
        @makho int
as
begin
        delete from khohang
        where makho = @makho
        
        if(@@error <> 0)
		begin
                rollback tran
                return
        end
end

exec sp_XoaKhoHang 9

--------------------------------------------------------
if object_id('sp_ThemHang', 'p') is not null
drop proc sp_ThemHang
go
create proc sp_ThemHang
@mahang int, @tenhang nvarchar(50), @nguoigui int, @nguoinhan nvarchar(5),@sodt nvarchar(50), @ngaygui datetime, @makho int, @soluong int, @trongluong int
as
begin
	if (@mahang = 0)
        insert into hang
        values(@tenhang, @nguoigui, @nguoinhan, @sodt, @ngaygui, @makho , @soluong, @trongluong)
    else
		update hang
		set	tenhang = @tenhang,
			nguoigui = @nguoigui,
			nguoinhan = @nguoinhan,
			sodt = @sodt,
			ngaygui = @ngaygui,
			makho = @makho,
			soluong = @soluong,
			trongluong = @trongluong
		where mahang = @mahang
    if(@@error <> 0)begin
                rollback tran
                return
    end
end
exec sp_ThemHang 9,'sdf 2','3','slkfjlsd', '80989899', '04/04/2012', '5', '30','50'

--------------------------------------------------------
if object_id('sp_XoaHang', 'p') is not null
drop proc sp_XoaHang
go
create proc sp_XoaHang
        @mahang int
as
begin
        delete from hang
        where mahang = @mahang        
        if(@@error <> 0)
		begin
                rollback tran
                return
        end
end

--------------------------------------------------------
if object_id('sp_DanhSachChuyen', 'p') is not null
drop proc sp_DanhSachChuyen
go
create proc sp_DanhSachChuyen
as
begin
	select * from chuyen	
	if(@@error <> 0)
	begin
		rollback tran
		return
    end
end


exec sp_DanhSachChuyen